package project.dao;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import models.util.MySqlDataAccessHelper;

import project.model.PrComposition;

public class PrCompositionDAO {
	public static List<PrComposition> getTesterProjectByID(String idProject,String database){
		List<PrComposition> lstComposition = new ArrayList<PrComposition>();
		MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
		try {
			
			PrComposition prComposition = null;
			helper.open(database);
			String sql = "SELECT c.IDComposition, c.IDEmployee, c.IDProject, c.Position, c.Status, e.UserName, e.FullName, " +
				" e.Position as PositionEmp FROM pr_composition c, employee e " +
				" WHERE c.IDProject = "+idProject+" AND c.IDEmployee = e.IDEmployee AND c.Position= 0";
			ResultSet rs = helper.executeQuery(sql);
			while(rs.next()){
				prComposition = new PrComposition(rs.getString("IDComposition"), rs.getString("IDProject"), rs.getString("IDEmployee"), 
            			rs.getInt("Position"), rs.getString("UserName"), rs.getString("FullName"), rs.getString("PositionEmp"), rs.getInt("Status"));
				lstComposition.add(prComposition);
			}
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return lstComposition;
	}

	public static List<PrComposition> getMemberProjectByID(String idProject,String database){
		List<PrComposition> lstComposition = new ArrayList<PrComposition>();
		MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
		try {
			
			PrComposition prComposition = null;
			helper.open(database);
			String sql = "SELECT c.IDComposition, c.IDEmployee, c.IDProject, c.Position, c.Status, e.UserName, e.FullName, " +
				" e.Position as PositionEmp FROM pr_composition c, employee e " +
				" WHERE c.IDProject = "+idProject+" AND c.IDEmployee = e.IDEmployee AND c.Position <> 0";
			ResultSet rs = helper.executeQuery(sql);
			while(rs.next()){
				prComposition = new PrComposition(rs.getString("IDComposition"), rs.getString("IDProject"), rs.getString("IDEmployee"), 
            			rs.getInt("Position"), rs.getString("UserName"), rs.getString("FullName"), rs.getString("PositionEmp"), rs.getInt("Status"));
				lstComposition.add(prComposition);
			}
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return lstComposition;
	}
	
	public int insertCoder(String db,PrComposition pc){
		int kq=-1;
		MySqlDataAccessHelper mysql = new MySqlDataAccessHelper();
		try{
			mysql.open(db);
			String sql="insert into pr_composition (IDComposition,IDProject,IDEmployee,Position,Status) " +
					"values('"+pc.getIdComposition()+"','"+pc.getIdProject()+"','"+pc.getIdEmployee()+"',1,0)";
			kq=mysql.executeUpdate(sql);
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			mysql.close();
		}
		return kq;
	}
	
	public int insertComMulti(String db,String s){
		int kq=-1;
		MySqlDataAccessHelper mysql = new MySqlDataAccessHelper();
		try{
			mysql.open(db);
			kq=mysql.executeUpdate(s);
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			mysql.close();
		}
		return kq;
	}
	
	public int insertTester(String db,PrComposition pc){
		int kq=-1;
		MySqlDataAccessHelper mysql = new MySqlDataAccessHelper();
		try{
			mysql.open(db);
			String sql="insert into pr_composition (IDComposition,IDProject,IDEmployee,Position,Status) " +
					"values('"+pc.getIdComposition()+"','"+pc.getIdProject()+"','"+pc.getIdEmployee()+"',0,0)";
			kq=mysql.executeUpdate(sql);
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			mysql.close();
		}
		return kq;
	}
	
	public boolean checkExitInsertCoder(String db,PrComposition pc){
		boolean kq=false;
		MySqlDataAccessHelper mysql = new MySqlDataAccessHelper();
		try{
			mysql.open(db);
			String sql="select pr_composition.IDComposition " +
					"from pr_composition " +
					"where pr_composition.IDProject='"+pc.getIdProject()+"' " +
							"and pr_composition.IDEmployee='"+pc.getIdEmployee()+"' " +
									"and pr_composition.Position=1";
			ResultSet rs= mysql.executeQuery(sql);
			if(rs.next()){
				kq=true;
			}
			rs.close();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			mysql.close();
		}
		return kq;
	}
	
	
	
	public boolean checkExitInsertCoderMulti(String db,String idpro, String idem){
		boolean kq=false;
		MySqlDataAccessHelper mysql = new MySqlDataAccessHelper();
		try{
			mysql.open(db);
			String sql="select pr_composition.IDComposition " +
					"from pr_composition " +
					"where pr_composition.IDProject='"+idpro+"' " +
							"and pr_composition.IDEmployee='"+idem+"' " +
									"and pr_composition.Position=1";
			ResultSet rs= mysql.executeQuery(sql);
			if(rs.next()){
				kq=true;
			}
			rs.close();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			mysql.close();
		}
		return kq;
	}
	
	public boolean checkExitInsertTester(String db,PrComposition pc){
		boolean kq=false;
		MySqlDataAccessHelper mysql = new MySqlDataAccessHelper();
		try{
			mysql.open(db);
			String sql="select pr_composition.IDComposition " +
					"from pr_composition " +
					"where pr_composition.IDProject='"+pc.getIdProject()+"' " +
							"and pr_composition.IDEmployee='"+pc.getIdEmployee()+"' " +
									"and pr_composition.Position=0";
			ResultSet rs= mysql.executeQuery(sql);
			if(rs.next()){
				kq=true;
			}
			rs.close();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			mysql.close();
		}
		return kq;
	}
	
	public boolean checkExitInsertTesterMulti(String db,String pr,String em){
		boolean kq=false;
		MySqlDataAccessHelper mysql = new MySqlDataAccessHelper();
		try{
			mysql.open(db);
			String sql="select pr_composition.IDComposition " +
					"from pr_composition " +
					"where pr_composition.IDProject='"+pr+"' " +
							"and pr_composition.IDEmployee='"+em+"' " +
									"and pr_composition.Position=0";
			ResultSet rs= mysql.executeQuery(sql);
			if(rs.next()){
				kq=true;
			}
			rs.close();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			mysql.close();
		}
		return kq;
	}
	
	public int deletePosCode(String db,String s,String pr){
		int kq=-1;
		MySqlDataAccessHelper mysql = new MySqlDataAccessHelper();
		try{
			mysql.open(db);
			String sql="update pr_composition " +
					"set pr_composition.Status=1 " +
					"where pr_composition.IDProject='"+pr+"' " +
							"and pr_composition.Position=1 " +
							"and pr_composition.IDEmployee in ("+s+")";
			//System.out.println("dl: "+sql);
			kq=mysql.executeUpdate(sql);
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			mysql.close();
		}
		return kq;
	}
	
	public int deletePosTest(String db,String s,String pr){
		int kq=-1;
		MySqlDataAccessHelper mysql = new MySqlDataAccessHelper();
		try{
			mysql.open(db);
			String sql="update pr_composition set pr_composition.Status=1 where pr_composition.IDProject='"+pr+"' and pr_composition.Position=0 and pr_composition.IDEmployee in ("+s+")";
			kq=mysql.executeUpdate(sql);
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			mysql.close();
		}
		return kq;
	}
	
	public int updatePosCode(String db,String em, String idpr){
		int kq=-1;
		MySqlDataAccessHelper mysql = new MySqlDataAccessHelper();
		try{
			mysql.open(db);
			String sql="update pr_composition " +
					"set pr_composition.Status=0 " +
					"where pr_composition.IDProject='"+idpr+"' " +
							" and pr_composition.IDEmployee='"+em+"' and pr_composition.Position=1 ";
			//System.out.println("cn: "+sql);
			kq=mysql.executeUpdate(sql);
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			mysql.close();
		}
		return kq;
	}
	
	public int updatePosCodeMulti(String db,String sql){
		int kq=-1;
		MySqlDataAccessHelper mysql = new MySqlDataAccessHelper();
		try{
			mysql.open(db);
			kq=mysql.executeUpdate(sql);
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			mysql.close();
		}
		return kq;
	}
	
	public int updatePosTest(String db, String em, String pr){
		int kq=-1;
		MySqlDataAccessHelper mysql = new MySqlDataAccessHelper();
		try{
			mysql.open(db);
			String sql="update pr_composition " +
					"set pr_composition.Status=0 " +
					"where pr_composition.IDProject='"+pr+"' " +
							"and pr_composition.IDEmployee='"+em+"' and pr_composition.Position=0 ";
			kq=mysql.executeUpdate(sql);
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			mysql.close();
		}
		return kq;
	}
}
